package com.bj58.groupbuy.action.service.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import com.bj58.groupbuy.action.db.CommonDao;
import com.bj58.groupbuy.action.db.DBConfig;
import com.bj58.groupbuy.action.db.DBConnectionPool;
import com.bj58.groupbuy.action.db.JdbcUitl;
import com.bj58.groupbuy.action.model.IRColumns;
import com.bj58.groupbuy.action.model.IRDrillConfig;
import com.bj58.groupbuy.action.service.IRDrillConfigService;
import com.bj58.groupbuy.action.util.MathEval;
import com.bj58.groupbuy.action.util.SortUtil;
import com.bj58.groupbuy.service.impl.BaseService;
import com.bj58.wf.log.Log;
import com.bj58.wf.log.LogFactory;

public class IRDrillConfigServiceImpl extends BaseService<IRDrillConfig>
		implements IRDrillConfigService {
	Log log = LogFactory.getLog(this.getClass());
	private CommonDao comDao = new CommonDao();

	@Override
	public List<IRDrillConfig> getListByIRid(int irReportId) throws Exception {
		List<IRDrillConfig> rsList = getGenericDao().find(IRDrillConfig.class,
				"*", "irReportId=" + irReportId, "id");
		if (null != rsList && !rsList.isEmpty())
			return rsList;

		return null;
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	@Override
	public List<String> getListForDrill(String Sql, List<Integer> colTypes,
			int type, HashMap<String, HashMap<String, Integer>> drillmap)
			throws Exception {
		// TODO Auto-generated method stub

		List<String> dataList = new ArrayList<String>();
		Connection con = DBConnectionPool.getConnection(type);
		if (null == con) {
			for (int i = 0; i < 2; i++) {
				log.info("try again " + i);
				con = DBConnectionPool.getConnection(type);
				if (null != con) {
					break;
				}
			}
		}
		Statement st = null;
		ResultSet rs = null;
		try {
			log.info("ir  con  is  " + con);
			st = con.createStatement();
			rs = st.executeQuery(Sql);
			while (rs.next()) {
				StringBuffer result = new StringBuffer();
				for (int i = 0; i < colTypes.size(); i++) {
					int colType = colTypes.get(i);
					boolean flag = false;
					String value = String.valueOf(rs.getObject(i + 1));
					if (null == value || "".equals(value)
							|| "null".equals(value)) {
						value = "--";
					}
					value = value.replace(",", "，");
					result.append(value + "," + colType + "," + value);
					// 如果group中包含下一级的钻取字段，则不允许本字段钻取
					// 添加钻取信息
					if (drillmap != null && drillmap.size() > 0)// drillmap类型：
					{
						Iterator iter = drillmap.entrySet().iterator();
						String drillcols = "";
						while (iter.hasNext()) {
							Map.Entry entry = (Map.Entry) iter.next();
							String key = entry.getKey().toString();
							HashMap<String, Integer> val = (HashMap<String, Integer>) entry
									.getValue();// 其中的integer类型存储在结果集中 的位置
							if (val.containsKey(key)&&val.get(key) == i)// 确定该字段是否需要钻取，需要钻取时其列数一致则需要钻取，比如get(key)=3,即只有第四列需要钻取，添加相应的信息
							{
								drillcols = "key:" + key + "#groupBy:";
								Iterator iterlevel1 = val.entrySet().iterator();
								while (iterlevel1.hasNext()) {
									Map.Entry entrylevel1 = (Map.Entry) iterlevel1
											.next();
									String keylevel1 = entrylevel1.getKey()
											.toString();
									drillcols += keylevel1
											+ "='"
											+ rs.getObject(Integer
													.parseInt(entrylevel1
															.getValue()
															.toString()) + 1)
											+ "'="
											+ entrylevel1.getValue().toString()
											+ " and ";
								}
								drillcols += "#sql:" + Sql;
								drillcols += "#colTypes:" + colTypes.toString();

								if (!val.containsKey("flag")) {
									result.append("," + drillcols);
									flag = true;
								}
							}
						}
					}
					if (flag == false) {
						result.append(",no");
					}
					if ((i + 1) < colTypes.size()) {

						result.append(";");
					}
				}
				dataList.add(result.toString());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUitl.closeResultSet(rs);
			JdbcUitl.closeStatement(st);
			JdbcUitl.closeConnection(con);
		}
		return dataList;
	}

	@Override
	public List<String> getListForMupDrill(List<String> tempList,
			List<String> cols, HashMap<String, IRColumns> irColMap,
			String groupby, int addcolssize) throws Exception {
		// TODO Auto-generated method stub
		List<Integer> spCols = new ArrayList<Integer>();// 特殊计算指标下标集
		List<Integer> norCols = new ArrayList<Integer>();// 正常处理的指标下标集
		List<Integer> keyCols = new ArrayList<Integer>();// key(groupby维度) 指标下标集

		Map<String, Integer> colMap = new HashMap<String, Integer>();// 字段名与下标MAP
																		// 后面为特殊计算指标公式替换用

		// 查询维度集合 ep: group by disp_city1_name,business_type_biz,disp_cate1_name
		String[] gbArrays = groupby.toLowerCase().trim().split(",");
		List<String> gbList = Arrays.asList(gbArrays);

		// 将查询项的cols按照字符长度倒序重排 用于后面的特殊计算公式指标替代 避免出现局部匹配替代
		List<String> sortCols = new ArrayList<String>();
		sortCols = SortUtil.sortListByLengthDesc(cols);

		// 指标分类
		for (int i = 0; i < cols.size(); i++) {
			String col = cols.get(i);
			if ("'total'".equals(col) || gbList.contains(col.toLowerCase())) {
				keyCols.add(i);
			} else {
				IRColumns column = irColMap.get(col);
				if (MathEval.checkHashEval(column.getSpecialInSql().replace(
						"/100", ""))) { // 检查查询SQL是否包含运算符 + - * / （去掉/100后）
					spCols.add(i);
				} else {
					norCols.add(i);
				}
			}
			colMap.put(col, i);
		}

		// 根据GROUPBY维度 组合KEY，将同维度数据放在一起
		Map<String, List<String>> tempMap = new HashMap<String, List<String>>();
		for (String temp : tempList) {
			String[] tempArrys = temp.split(";");
			StringBuffer key = new StringBuffer();
			for (int index : keyCols) {
				String nodrillkey = tempArrys[index];
				// 如果有钻取条件，去掉钻取条件参数
				if (tempArrys[index].contains(",key:")) {
					nodrillkey = tempArrys[index].substring(0,
							tempArrys[index].indexOf(",key:"));

				}
				key.append(nodrillkey + ";");
			}

			List<String> dataList = new ArrayList<String>();
			if (tempMap.containsKey(key.toString())) {
				dataList = tempMap.get(key.toString());
				dataList.add(temp);
			} else {
				dataList.add(temp);
				tempMap.put(key.toString(), dataList);
			}
		}

		List<String> finalList = new ArrayList<String>();// 最终结果集
		// 计算数据
		for (Iterator<String> it = tempMap.keySet().iterator(); it.hasNext();) {
			try {
				String key = it.next();
				List<String> dataList = tempMap.get(key);
				String[] dataOneArray = dataList.get(0).split(";"); // 将第一列数据取出
																	// 备用
																	// 取key类型和字符串数据用

				StringBuffer tempValues = new StringBuffer();

				TreeMap<String, Object> colValueMap = new TreeMap<String, Object>(); // 指标名与值对应MAP
																						// 后面处理特殊计算规则指标时
																						// 替换公式用
				for (int i = 0; i < cols.size(); i++) {
					int colType = Integer
							.parseInt(dataOneArray[i].split(",")[1]); // 字段数据类型
																		// 0:字符
																		// 1:整形
																		// 2:浮点
																		// 3：百分比
					if (keyCols.contains(i)) { // 维度指标
						/**
						 * 钻取条件处理，添加表名集合和日期集合,钻取条件只可能存在维度中,不考虑日期，只存储表名，
						 * 添加一个tablelist
						 * */
						String dataoneresult = dataOneArray[i];
						if (dataOneArray[i].split(",").length > 3
								&& !dataOneArray[i].split(",")[3].trim()
										.equals("no")
								&& !dataOneArray[i].contains("#tablelist:")) // 如果该列可钻取
						{
							String tablelist = "#tablelist:";// 一个表对应一个日期,注意定义区域
							for (String data : dataList) {
								String[] dataArray = data.split(";");
								String tablename = dataArray[i].substring(
										dataArray[i].indexOf("from "),
										dataArray[i].indexOf(" where"));
								tablelist = tablelist + "@" + tablename;
							}
							dataoneresult = dataoneresult + tablelist
									+ "#addcolssize:" + addcolssize;
						}
						tempValues.append(dataoneresult + ";");
					} else if (spCols.contains(i)) { // 计算公式数据待依赖的数据重算完毕后再算 先占位
						tempValues.append("todo," + colType + ";");
					} else {
						if (0 == colType) { // 字符则直接取默认第一个
							tempValues.append(dataOneArray[i] + ";");
						} else { // 其他类型数据直接sum
							double sumValue = 0;
							for (String data : dataList) {
								String[] dataArray = data.split(";");
								sumValue = sumValue
										+ Double.valueOf(dataArray[i]
												.split(",")[0]);
							}

							// 不以科学计数
							NumberFormat nf = NumberFormat.getInstance();
							nf.setGroupingUsed(false);
							String tranSumValue = nf.format(sumValue);

							tempValues.append(tranSumValue + "," + colType
									+ "," + tranSumValue + ";");
							colValueMap.put(cols.get(i), tranSumValue);
						}
					}
				}

				List<String> tempValueList = new ArrayList<String>();
				for (String value : tempValues.toString().split(";")) {
					tempValueList.add(value);
				}

				DecimalFormat df = new DecimalFormat("####.##");

				// 特殊计算公式数据处理
				for (int index : spCols) {
					String colName = cols.get(index); // 指标名
					IRColumns column = irColMap.get(colName);
					String formula = column.getSpecialInSql().toLowerCase(); // 计算公式

					for (String col : sortCols) {
						if (formula.contains(col.toLowerCase())) {
							Object value = colValueMap.get(col);
							formula = formula.replace(col.toLowerCase(),
									df.format(Double.parseDouble(value
											.toString())));
						}
					}

					Object tempRS = comDao.selectObject("select " + formula,
							DBConfig.db_68_bi_sys.getIntType()); // 用SQL查询进行运算
					String rs = "0";
					if (null != tempRS) {
						rs = tempRS.toString();
					}
					int colType = Integer.parseInt(tempValueList.get(index)
							.split(",")[1]);
					tempValueList.remove(index);
					tempValueList.add(index, rs + "," + colType + "," + rs);
					colValueMap.put(colName, rs);

				}

				// 最终结果
				StringBuffer finalRS = new StringBuffer();
				for (int i = 0; i < tempValueList.size(); i++) {
					String r = tempValueList.get(i);
					finalRS.append(r);
					if ((i + 1) < tempValueList.size()) {
						finalRS.append(";");
					}
				}
				finalList.add(finalRS.toString());
			} catch (Exception e) {
				e.printStackTrace();
				continue;
			}

		}

		return finalList;
	}

}
